SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE [dbo].[UnsettledTrades]
	-- Add the parameters for the stored procedure here
	@FundId VARCHAR(10)
AS
BEGIN
	
	SET NOCOUNT ON;

SELECT  assum.ActivityType 'Type' ,
        p.PortfolioName 'Portfolio' ,
        i.InstrName 'Asset' ,
        i.extid 'External ID' ,
        assum.BlotterId 'Blotter ID' ,
        t.TradeAmount 'Par' ,
        assum.TradePrice 'Trade Price' ,
        t.Proceeds 'Proceeds' ,
        assum.ActivityDate 'Trade Date' ,
        t.SettlementDate 'Settle Date'
FROM    FSFundModel.Reporting.Trade t
        INNER JOIN FSFundModel.Reporting.Asset a ON t.ExportId = a.ExportId
                                        AND t.InstrumentId = a.InstrumentId
                                        AND t.LotId = a.LotId
                                        AND t.PortfolioId = a.PortfolioId
        INNER JOIN FSFundModel.Reporting.Instrument i ON a.InstrumentId = i.InstrumentId
                                             AND t.ExportId = i.ExportId
        INNER JOIN FSFundModel.Reporting.Portfolio p ON t.PortfolioId = p.PortfolioId
                                            AND t.ExportId = p.ExportId
        INNER JOIN FSFundModel.Reporting.ActivitySummary assum ON assum.ExportId = t.ExportId
                                                      AND assum.InstrumentId = t.InstrumentId
                                                      AND t.ActivitySummaryId = assum.ActivitySummaryId
WHERE   t.ExportId = ( SELECT   MAX(e.ExportId)
                       FROM     FSFundModel.Reporting.Export e
                       WHERE    e.FundId = @FundId
                                
                     )
      
        AND t.IsSettled = 0
        AND assum.activitytype <> 'CashlessTransfer'
ORDER BY assum.ActivityType ASC ,
        p.SortOrder ASC ,
        assum.ActivityDate DESC ,
        i.InstrName ASC 


    
END

GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Live/Daily Reporting', 'SCHEMA', N'dbo', 'PROCEDURE', N'UnsettledTrades', NULL, NULL
GO
